--编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。
create view v_information
as
select AccountCode,CardNo,RealName,CardMoney from AccountInfo inner join BankCard on BankCard.AccountId=AccountInfo.AccountId
go
select * from v_information
drop view v_information



--行转列
create table huatai(
	year int,
	month int,
	amount float
)

insert into huatai(year,month,amount) values (1991,1,1.1)
insert into huatai(year,month,amount) values (1991,2,1.2)
insert into huatai(year,month,amount) values (1991,3,1.3)
insert into huatai(year,month,amount) values (1991,4,1.4)

insert into huatai(year,month,amount) values (1992,1,2.1)
insert into huatai(year,month,amount) values (1992,2,2.2)
insert into huatai(year,month,amount) values (1992,3,2.3)
insert into huatai(year,month,amount) values (1992,4,2.4)

select * from huatai


create view m1
as
select year,amount m1 from huatai where month=1
go
create view m2
as
select year,amount m2 from huatai where month=2
go
create view m3
as
select year,amount m3 from huatai where month=3
go
create view m4
as
select year,amount m4 from huatai where month=4
go

select m1.year,m1,m2,m3,m4 from m1 
join m2 on m2.year=m1.year
join m3 on m3.year=m1.year
join m4 on m4.year=m1.year

drop view m1
drop view m2
drop view m3
drop view m4


select * from huatai pivot(max(amount) for month in ([1],[2],[3],[4])) a

select * from huatai pivot(min(amount) for month in ([1],[2],[3],[4])) b